<?php
function updateSummarised($terms, $value)
{
    global $db;
    $sql = <<< EOT
        UPDATE PublicationPerson
            SET Summarised = %d
        WHERE %s
EOT;
    $sql = sprintf($sql, $value, $terms);
    $result = $db->query($sql);
    return $result === TRUE;
}
function updateSummary($transId, $summary)
{
    global $db;
    $sql = <<< EOT
        UPDATE Person
            SET NoticeSummary = '%s'
        WHERE Person.ID = (
            SELECT TxnData1
            FROM DPSTransaction
            WHERE
                TxnType = 'Notice Summary' AND
                ID = %d
        )
EOT;
    $sql = sprintf($sql, $summary, $transId);
    $result = $db->query($sql);
    return $result === TRUE;
}
function getPersonInfo($personId)
{
    global $db;
    $personInfo = array();
    $sql = <<< EOT
        SELECT
            Person.ID, Surname, FirstNames, AlsoKnownAs, NeeName, FormerName, DeathDate, BirthDate, BirthDate, Age, AgeMeasure, Country.Name as Country, CityTown.Name as CityTown, CityTown.Name as FormerCityTown, Charity.Name as Charity, FuneralHome.Name as FuneralHome, FuneralDetails.Web as FuneralUrl, NoticeSummary
        FROM Person
            LEFT OUTER JOIN Country
                ON Country.ID = CountryId
            LEFT OUTER JOIN CityTown
                ON CityTown.ID = CityTownId
            LEFT OUTER JOIN CityTown as FormerCityTown
                ON CityTown.ID = FormerCityTownID
            LEFT OUTER JOIN Charity
                ON Charity.ID = CharityID
            LEFT OUTER JOIN FuneralHome
                ON FuneralHome.ID = FuneralHomeID
            LEFT OUTER JOIN FuneralDetails
                ON FuneralDetails.FuneralHomeID = FuneralHome.ID
        WHERE Person.ID = %d
EOT;
    $sql = sprintf($sql, $personId);
    $result = $db->query($sql);
    if ($db->rowCount($result) > 0) {
        $row = $db->fetch($result);
        $personInfo = array(
            'id' => intval($row['ID']),
            'lastName' => $db->unescape($row['Surname']),
            'firstName' => $db->unescape($row['FirstNames']),
            'akaName' => $db->unescape($row['AlsoKnownAs']),
            'neeName' => $db->unescape($row['NeeName']),
            'formerName' => $db->unescape($row['FormerName']),
            'deathDate' => $db->unescape($row['DeathDate']),
            'birthDate' => $db->unescape($row['BirthDate']),
            'age' => $db->unescape($row['Age']),
            'ageMeasure' => $db->unescape($row['AgeMeasure']),
            'country' => $db->unescape($row['Country']),
            'cityTown' => $db->unescape($row['CityTown']),
            'formerCityTown' => $db->unescape($row['FormerCityTown']),
            'charity' => $db->unescape($row['Charity']),
            'funeralHome' => $db->unescape($row['FuneralHome']),
            'noticeSummary' => $db->unescape($row['NoticeSummary'])
        );
    }
    return $personInfo;
}
function getTransInfo($transId)
{
    global $db;
    $transInfo = array();
    $sql = <<< EOT
        SELECT ID, TxnData1, TxnData2, TxnData3, Success, Processed
        FROM DPSTransaction
        WHERE
            TxnType = 'Notice Summary' AND
            ID = %d
EOT;
    $sql = sprintf($sql, $transId);
    $result = $db->query($sql);
    if ($db->rowCount($result) > 0) {
        $row = $db->fetch($result);
        $transInfo['id'] = intval($row['ID']);
        $transInfo['txnData1'] = $db->unescape($row['TxnData1']);
        $transInfo['txnData2'] = $db->unescape($row['TxnData2']);
        $transInfo['txnData3'] = $db->unescape($row['TxnData3']);
        $transInfo['success'] = intval($row['Success']);
        $transInfo['processed'] = intval($row['Processed']);
    }
    return $transInfo;
}
function getNewspaperInfo($personId, $paperName)
{
    global $db;
    $newspaperInfo = array();
    $sql = <<< EOT
        SELECT PublicationPerson.ID, Publication.Name, Publication.DatePublished, PublicationPerson.Summarised
        FROM PublicationPerson
            INNER JOIN Publication
                ON PublicationPerson.PublicationID = Publication.ID
            INNER JOIN Newspapers
                ON Newspapers.Name = '%s'
        WHERE PersonID = %d and Publication.Name LIKE CONCAT('%%', Newspapers.ShortName, '%%')
        ORDER BY Publication.DatePublished ASC
EOT;
    $sql = sprintf($sql, $paperName, $personId);
    $result = $db->query($sql);
    if ($db->rowCount($result) > 0) {
        while (1) {
            $row = $db->fetch($result);
            if ($row === FALSE) {
                break;
            }
            $newspaper['id'] = $db->unescape($row['ID']);
            $newspaper['name'] = $db->unescape($row['Name']);
            $newspaper['date'] = $db->unescape($row['DatePublished']);
            $newspaper['summarised'] = intval($row['Summarised']);
            $newspaperInfo[] = $newspaper;
        }
    }
    return $newspaperInfo;
}
function getPublicationsByTransaction($transId)
{
    global $db;
    $publications = array();
    $sql = <<< EOT
    SELECT PublicationPerson.ID FROM PublicationPerson
        INNER JOIN DPSTransaction
            ON DPSTransaction.TxnData1 = PublicationPerson.PersonID
    WHERE
        DPSTransaction.TxnType = 'Notice Summary' AND
        DPSTransaction.ID = %d
EOT;
    $sql = sprintf($sql, $transId);
    $result = $db->query($sql);
    if ($db->rowCount($result) > 0) {
        while (1) {
            $row = $db->fetch($result);
            if (empty($row)) {
                break;
            }
            $publications[] = $row['ID'];
        }
    }
    return $publications;
    
}
function updateProcessed($transId, $updated)
{
    global $db;
    $sql = <<< EOT
        UPDATE DPSTransaction
            SET Processed = %d
        WHERE
            TxnType = 'Notice Summary' AND
            ID = %d
EOT;
    $sql = sprintf($sql, $updated, $transId);
    $result = $db->query($sql);
    return $result === TRUE;
}
?>